<!DOCTYPE html><html><head><title>MySQL学习笔记（Day017：Explain_2）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-2346" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2347" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day017explain2">MySQL学习笔记（Day017：Explain_2）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day017explain2">MySQL学习笔记（Day017：Explain_2）</a><ul>
<li><a href="#一-作业解析">一. 作业解析</a></li>
<li><a href="#二-mysql56安装sys库">二. MySQL5.6安装sys库</a></li>
<li><a href="#三-explain二">三. Explain（二）</a><ul>
<li><a href="#1-explain输出介绍">1. Explain输出介绍</a><ul>
<li><a href="#1-id">（1）. id</a></li>
<li><a href="#2-selecttype">（2）. select_type</a></li>
<li><a href="#3-table">（3）. table</a></li>
<li><a href="#4-type">（4）. type</a></li>
<li><a href="#5-extra">（5）. extra</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-2348" class="wmd-preview-section preview-content">

<h2 id="一-作业解析">一. 作业解析</h2>

<ul><li><strong>哪张原数据表中记录了Cardinality信息</strong></li>
</ul>

</div><div id="wmd-preview-section-2349" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 在information_schema.STATISTICS中记录了相关的信息</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> information_schema;</span><br><span class="hljs-comment line-number">5.</span>Database changed<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">STATISTICS</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">8.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">9.</span>       <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">10.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TEMPORARY</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`STATISTICS`</span> (<br><span class="hljs-comment line-number">11.</span>  <span class="hljs-string">`TABLE_CATALOG`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">512</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">12.</span>  <span class="hljs-string">`TABLE_SCHEMA`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,  <span class="hljs-comment">-- 表所在的库</span><br><span class="hljs-comment line-number">13.</span>  <span class="hljs-string">`TABLE_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,  <span class="hljs-comment">-- 表名</span><br><span class="hljs-comment line-number">14.</span>  <span class="hljs-string">`NON_UNIQUE`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'0'</span>,<br><span class="hljs-comment line-number">15.</span>  <span class="hljs-string">`INDEX_SCHEMA`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>, <br><span class="hljs-comment line-number">16.</span>  <span class="hljs-string">`INDEX_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,  <span class="hljs-comment">-- 索引名</span><br><span class="hljs-comment line-number">17.</span>  <span class="hljs-string">`SEQ_IN_INDEX`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">2</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'0'</span>, <span class="hljs-comment">-- 索引的序号</span><br><span class="hljs-comment line-number">18.</span>  <span class="hljs-string">`COLUMN_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">19.</span>  <span class="hljs-string">`COLLATION`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">20.</span>  <span class="hljs-string">`CARDINALITY`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,   <span class="hljs-comment">-- 这里我们找到了Cardinality</span><br><span class="hljs-comment line-number">21.</span>  <span class="hljs-string">`SUB_PART`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">22.</span>  <span class="hljs-string">`PACKED`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">23.</span>  <span class="hljs-string">`NULLABLE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">24.</span>  <span class="hljs-string">`INDEX_TYPE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">16</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">25.</span>  <span class="hljs-string">`COMMENT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">16</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">26.</span>  <span class="hljs-string">`INDEX_COMMENT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">1024</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span><br><span class="hljs-comment line-number">27.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">MEMORY</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8<br><span class="hljs-comment line-number">28.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">29.</span><br><span class="hljs-comment line-number">30.</span><br><span class="hljs-comment line-number">31.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">32.</span><span class="hljs-comment">-- 之前我们可以通过 show index from table_name的方式查看索引</span><br><span class="hljs-comment line-number">33.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span>mysql&gt;  <span class="hljs-keyword">show</span> <span class="hljs-keyword">index</span> <span class="hljs-keyword">from</span> employees.salaries\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">36.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">37.</span>        <span class="hljs-keyword">Table</span>: salaries<br><span class="hljs-comment line-number">38.</span>   Non_unique: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">39.</span>     Key_name: PRIMARY<br><span class="hljs-comment line-number">40.</span> Seq_in_index: <span class="hljs-number">1</span>       <span class="hljs-comment">-- 索引序号为1</span><br><span class="hljs-comment line-number">41.</span>  Column_name: emp_no<br><span class="hljs-comment line-number">42.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">43.</span>  Cardinality: <span class="hljs-number">286271</span>  <span class="hljs-comment">-- Cardinality值</span><br><span class="hljs-comment line-number">44.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">45.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">46.</span>         <span class="hljs-literal">Null</span>: <br><span class="hljs-comment line-number">47.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">48.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">49.</span>Index_comment: <br><span class="hljs-comment line-number">50.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">51.</span>        <span class="hljs-keyword">Table</span>: salaries<br><span class="hljs-comment line-number">52.</span>   Non_unique: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">53.</span>     Key_name: PRIMARY<br><span class="hljs-comment line-number">54.</span> Seq_in_index: <span class="hljs-number">2</span>         <span class="hljs-comment">-- 索引序号为2</span><br><span class="hljs-comment line-number">55.</span>  Column_name: from_date<br><span class="hljs-comment line-number">56.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">57.</span>  Cardinality: <span class="hljs-number">2760952</span>   <span class="hljs-comment">-- Cardinality值</span><br><span class="hljs-comment line-number">58.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">59.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">60.</span>         <span class="hljs-literal">Null</span>: <br><span class="hljs-comment line-number">61.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">62.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">63.</span>Index_comment: <br><span class="hljs-comment line-number">64.</span><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">65.</span><br><span class="hljs-comment line-number">66.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">67.</span><span class="hljs-comment">-- 现在可以通过STATISTICS表查看某张表的信息</span><br><span class="hljs-comment line-number">68.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">69.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">STATISTICS</span> <span class="hljs-keyword">where</span> table_name=<span class="hljs-string">'salaries'</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">70.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">71.</span>TABLE_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">72.</span> TABLE_SCHEMA: employees<br><span class="hljs-comment line-number">73.</span>   TABLE_NAME: salaries<br><span class="hljs-comment line-number">74.</span>   NON_UNIQUE: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">75.</span> INDEX_SCHEMA: employees<br><span class="hljs-comment line-number">76.</span>   INDEX_NAME: PRIMARY<br><span class="hljs-comment line-number">77.</span> SEQ_IN_INDEX: <span class="hljs-number">1</span>        <span class="hljs-comment">-- 索引序号为1</span><br><span class="hljs-comment line-number">78.</span>  COLUMN_NAME: emp_no<br><span class="hljs-comment line-number">79.</span>    <span class="hljs-keyword">COLLATION</span>: A<br><span class="hljs-comment line-number">80.</span>  CARDINALITY: <span class="hljs-number">286271</span>   <span class="hljs-comment">-- Cardinality值</span><br><span class="hljs-comment line-number">81.</span>     SUB_PART: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">82.</span>       PACKED: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">83.</span>     NULLABLE: <br><span class="hljs-comment line-number">84.</span>   INDEX_TYPE: BTREE<br><span class="hljs-comment line-number">85.</span>      <span class="hljs-keyword">COMMENT</span>: <br><span class="hljs-comment line-number">86.</span>INDEX_COMMENT: <br><span class="hljs-comment line-number">87.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">88.</span>TABLE_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">89.</span> TABLE_SCHEMA: employees<br><span class="hljs-comment line-number">90.</span>   TABLE_NAME: salaries<br><span class="hljs-comment line-number">91.</span>   NON_UNIQUE: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">92.</span> INDEX_SCHEMA: employees<br><span class="hljs-comment line-number">93.</span>   INDEX_NAME: PRIMARY<br><span class="hljs-comment line-number">94.</span> SEQ_IN_INDEX: <span class="hljs-number">2</span>          <span class="hljs-comment">-- 索引序号为2</span><br><span class="hljs-comment line-number">95.</span>  COLUMN_NAME: from_date<br><span class="hljs-comment line-number">96.</span>    <span class="hljs-keyword">COLLATION</span>: A<br><span class="hljs-comment line-number">97.</span>  CARDINALITY: <span class="hljs-number">2760952</span>    <span class="hljs-comment">-- Cardinality值</span><br><span class="hljs-comment line-number">98.</span>     SUB_PART: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">99.</span>       PACKED: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">100.</span>     NULLABLE: <br><span class="hljs-comment line-number">101.</span>   INDEX_TYPE: BTREE<br><span class="hljs-comment line-number">102.</span>      <span class="hljs-keyword">COMMENT</span>: <br><span class="hljs-comment line-number">103.</span>INDEX_COMMENT: <br><span class="hljs-comment line-number">104.</span><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">105.</span><br><span class="hljs-comment line-number">106.</span><span class="hljs-comment">---</span><br><span class="hljs-comment line-number">107.</span><span class="hljs-comment">---  可以看出，上面两个方法得到的Cardinality的值是相等</span><br><span class="hljs-comment line-number">108.</span><span class="hljs-comment">---  结论就是information_schema.STATISTICS这张表记录了Cardinality信息</span><br><span class="hljs-comment line-number">109.</span><span class="hljs-comment">---</span></span><br></code></pre>

<ul><li><strong>检查表的索引创建的情况，判断该索引是否有创建的必要</strong></li>
</ul>

</div><div id="wmd-preview-section-2350" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 1. 表的信息如table_schema, table_name, table_rows等</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--    在information_schema.TABLES中</span><br><span class="hljs-comment line-number">4.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">5.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">TABLES</span>\<span class="hljs-keyword">G</span>  <br><span class="hljs-comment line-number">6.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">7.</span>       <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">TABLES</span><br><span class="hljs-comment line-number">8.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TEMPORARY</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`TABLES`</span> (<br><span class="hljs-comment line-number">9.</span>  <span class="hljs-string">`TABLE_CATALOG`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">512</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">10.</span>  <span class="hljs-string">`TABLE_SCHEMA`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,   <span class="hljs-comment">-- 表所在的库</span><br><span class="hljs-comment line-number">11.</span>  <span class="hljs-string">`TABLE_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,     <span class="hljs-comment">-- 表名</span><br><span class="hljs-comment line-number">12.</span>  <span class="hljs-string">`TABLE_TYPE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">13.</span>  <span class="hljs-string">`ENGINE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">14.</span>  <span class="hljs-string">`VERSION`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">15.</span>  <span class="hljs-string">`ROW_FORMAT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">16.</span>  <span class="hljs-string">`TABLE_ROWS`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,    <span class="hljs-comment">-- 表的记录数</span><br><span class="hljs-comment line-number">17.</span>  <span class="hljs-string">`AVG_ROW_LENGTH`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">18.</span>  <span class="hljs-string">`DATA_LENGTH`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">19.</span>  <span class="hljs-string">`MAX_DATA_LENGTH`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">20.</span>  <span class="hljs-string">`INDEX_LENGTH`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">21.</span>  <span class="hljs-string">`DATA_FREE`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">22.</span>  <span class="hljs-string">`AUTO_INCREMENT`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">23.</span>  <span class="hljs-string">`CREATE_TIME`</span> datetime <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">24.</span>  <span class="hljs-string">`UPDATE_TIME`</span> datetime <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">25.</span>  <span class="hljs-string">`CHECK_TIME`</span> datetime <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">26.</span>  <span class="hljs-string">`TABLE_COLLATION`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">27.</span>  <span class="hljs-string">`CHECKSUM`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">28.</span>  <span class="hljs-string">`CREATE_OPTIONS`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">29.</span>  <span class="hljs-string">`TABLE_COMMENT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">2048</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span><br><span class="hljs-comment line-number">30.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">MEMORY</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8<br><span class="hljs-comment line-number">31.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">32.</span><br><span class="hljs-comment line-number">33.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">34.</span><span class="hljs-comment">-- 2. information.STATISTICS中存在 table_schema 和 table_name 信息</span><br><span class="hljs-comment line-number">35.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">36.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">STATISTICS</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">37.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">38.</span>       <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">39.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TEMPORARY</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`STATISTICS`</span> (<br><span class="hljs-comment line-number">40.</span>  <span class="hljs-string">`TABLE_CATALOG`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">512</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">41.</span>  <span class="hljs-string">`TABLE_SCHEMA`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,  <span class="hljs-comment">-- 表所在的库</span><br><span class="hljs-comment line-number">42.</span>  <span class="hljs-string">`TABLE_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,  <span class="hljs-comment">-- 表名</span><br><span class="hljs-comment line-number">43.</span>  <span class="hljs-string">`NON_UNIQUE`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'0'</span>,<br><span class="hljs-comment line-number">44.</span>  <span class="hljs-string">`INDEX_SCHEMA`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>, <br><span class="hljs-comment line-number">45.</span>  <span class="hljs-string">`INDEX_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,   <span class="hljs-comment">-- 索引名</span><br><span class="hljs-comment line-number">46.</span>  <span class="hljs-string">`SEQ_IN_INDEX`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">2</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'0'</span>, <br><span class="hljs-comment line-number">47.</span>  <span class="hljs-string">`COLUMN_NAME`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">48.</span>  <span class="hljs-string">`COLLATION`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">49.</span>  <span class="hljs-string">`CARDINALITY`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">21</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <br><span class="hljs-comment line-number">50.</span>  <span class="hljs-string">`SUB_PART`</span> <span class="hljs-built_in">bigint</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">51.</span>  <span class="hljs-string">`PACKED`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">52.</span>  <span class="hljs-string">`NULLABLE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">53.</span>  <span class="hljs-string">`INDEX_TYPE`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">16</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>,<br><span class="hljs-comment line-number">54.</span>  <span class="hljs-string">`COMMENT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">16</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">55.</span>  <span class="hljs-string">`INDEX_COMMENT`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">1024</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span><br><span class="hljs-comment line-number">56.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">MEMORY</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8<br><span class="hljs-comment line-number">57.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">58.</span><br><span class="hljs-comment line-number">59.</span><br><span class="hljs-comment line-number">60.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">61.</span><span class="hljs-comment">-- 3. 将TABLES 和 STATISTICS 表中的table_schema和table_name相关联</span><br><span class="hljs-comment line-number">62.</span><span class="hljs-comment">--    通过Cardinality和table_rows 计算，即可得到对应索引名的 选择性</span><br><span class="hljs-comment line-number">63.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">64.</span><br><span class="hljs-comment line-number">65.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">66.</span><span class="hljs-comment">-- 3.1 因为存在复合索引，所以我们要取出复合索引中seq最大的哪个值</span><br><span class="hljs-comment line-number">67.</span><span class="hljs-comment">--     这样取出的cardinality值才是最大的</span><br><span class="hljs-comment line-number">68.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">69.</span>mysql&gt; <span class="hljs-keyword">select</span> <br><span class="hljs-comment line-number">70.</span>    -&gt;     table_schema, table_name, index_name,<br><span class="hljs-comment line-number">71.</span>    -&gt;     <span class="hljs-keyword">max</span>(seq_in_index)  <span class="hljs-comment">-- 取出最大的seq号后，选出index_name等信息</span><br><span class="hljs-comment line-number">72.</span>    -&gt; <span class="hljs-keyword">from</span><br><span class="hljs-comment line-number">73.</span>    -&gt;     <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">74.</span>    -&gt; <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> table_schema , table_name , index_name\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">75.</span><br><span class="hljs-comment line-number">76.</span><span class="hljs-comment">--  -----------省略其他输出-----------</span><br><span class="hljs-comment line-number">77.</span>*************************** <span class="hljs-number">10.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">78.</span>     table_schema: burn_test<br><span class="hljs-comment line-number">79.</span>       table_name: test_index_2<br><span class="hljs-comment line-number">80.</span>       index_name: idx_mul_ab   <span class="hljs-comment">-- 这个是上次测试复合索引建立的index</span><br><span class="hljs-comment line-number">81.</span><span class="hljs-keyword">max</span>(seq_in_index): <span class="hljs-number">2</span>            <span class="hljs-comment">-- 取出了最大的seq</span><br><span class="hljs-comment line-number">82.</span><span class="hljs-comment">--  -----------省略其他输出-----------</span><br><span class="hljs-comment line-number">83.</span><br><span class="hljs-comment line-number">84.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">85.</span><span class="hljs-comment">--  3.2 得到了最大的seq，从而可以取出对应的cardinality</span><br><span class="hljs-comment line-number">86.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">87.</span><br><span class="hljs-comment line-number">88.</span>mysql&gt; <span class="hljs-keyword">select</span> <br><span class="hljs-comment line-number">89.</span>    -&gt;     table_schema, table_name, index_name, cardinality<br><span class="hljs-comment line-number">90.</span>    -&gt; <span class="hljs-keyword">from</span><br><span class="hljs-comment line-number">91.</span>    -&gt;     <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">92.</span>    -&gt; <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">93.</span>    -&gt;     (table_schema , table_name, index_name, seq_in_index) <span class="hljs-keyword">in</span> <br><span class="hljs-comment line-number">94.</span>    -&gt;        (<span class="hljs-keyword">select</span> <br><span class="hljs-comment line-number">95.</span>    -&gt;             table_schema, table_name, <br><span class="hljs-comment line-number">96.</span>    -&gt;             index_name, <span class="hljs-keyword">max</span>(seq_in_index)<br><span class="hljs-comment line-number">97.</span>    -&gt;         <span class="hljs-keyword">from</span><br><span class="hljs-comment line-number">98.</span>    -&gt;             <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">99.</span>    -&gt;         <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> table_schema , table_name , index_name)\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">100.</span><br><span class="hljs-comment line-number">101.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">102.</span>table_schema: burn_test<br><span class="hljs-comment line-number">103.</span>  table_name: Orders<br><span class="hljs-comment line-number">104.</span>  index_name: PRIMARY<br><span class="hljs-comment line-number">105.</span> cardinality: <span class="hljs-number">5</span><br><span class="hljs-comment line-number">106.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">107.</span>table_schema: burn_test<br><span class="hljs-comment line-number">108.</span>  table_name: Orders_MV<br><span class="hljs-comment line-number">109.</span>  index_name: product_name<br><span class="hljs-comment line-number">110.</span> cardinality: <span class="hljs-number">3</span><br><span class="hljs-comment line-number">111.</span>*************************** <span class="hljs-number">3.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">112.</span>table_schema: burn_test<br><span class="hljs-comment line-number">113.</span>  table_name: <span class="hljs-keyword">child</span><br><span class="hljs-comment line-number">114.</span>  index_name: par_ind<br><span class="hljs-comment line-number">115.</span> cardinality: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">116.</span>*************************** <span class="hljs-number">4.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">117.</span>table_schema: burn_test<br><span class="hljs-comment line-number">118.</span>  table_name: <span class="hljs-keyword">parent</span><br><span class="hljs-comment line-number">119.</span>  index_name: PRIMARY<br><span class="hljs-comment line-number">120.</span> cardinality: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">121.</span>*************************** <span class="hljs-number">5.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">122.</span>table_schema: burn_test<br><span class="hljs-comment line-number">123.</span>  table_name: t4<br><span class="hljs-comment line-number">124.</span>  index_name: PRIMARY<br><span class="hljs-comment line-number">125.</span> cardinality: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">126.</span><br><span class="hljs-comment line-number">127.</span><span class="hljs-comment">--  -----------省略其他输出-----------</span><br><span class="hljs-comment line-number">128.</span><br><span class="hljs-comment line-number">129.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">130.</span><span class="hljs-comment">-- 3.3 最后通过table_schema和table_name 让上述的信息和TABLES表进行关联</span><br><span class="hljs-comment line-number">131.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">132.</span><br><span class="hljs-comment line-number">133.</span><span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">134.</span>     <span class="hljs-keyword">t</span>.TABLE_SCHEMA,<span class="hljs-keyword">t</span>.TABLE_NAME,INDEX_NAME, CARDINALITY, TABLE_ROWS, <br><span class="hljs-comment line-number">135.</span>     CARDINALITY/TABLE_ROWS <span class="hljs-keyword">AS</span> SELECTIVITY  <span class="hljs-comment">-- 得到选择性</span><br><span class="hljs-comment line-number">136.</span><span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">137.</span>    <span class="hljs-keyword">TABLES</span> <span class="hljs-keyword">t</span>,  <span class="hljs-comment">-- 查询的表一，TABLES</span><br><span class="hljs-comment line-number">138.</span>    (<br><span class="hljs-comment line-number">139.</span>        <span class="hljs-keyword">SELECT</span>     <br><span class="hljs-comment line-number">140.</span>            table_schema,<br><span class="hljs-comment line-number">141.</span>            table_name,<br><span class="hljs-comment line-number">142.</span>            index_name,<br><span class="hljs-comment line-number">143.</span>            cardinality<br><span class="hljs-comment line-number">144.</span>        <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">STATISTICS</span> <br><span class="hljs-comment line-number">145.</span>        <span class="hljs-keyword">WHERE</span> (table_schema,table_name,index_name,seq_in_index) <span class="hljs-keyword">IN</span> (<br><span class="hljs-comment line-number">146.</span>        <span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">147.</span>            table_schema,<br><span class="hljs-comment line-number">148.</span>            table_name,<br><span class="hljs-comment line-number">149.</span>            index_name,<br><span class="hljs-comment line-number">150.</span>            <span class="hljs-keyword">MAX</span>(seq_in_index)<br><span class="hljs-comment line-number">151.</span>        <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">152.</span>            <span class="hljs-keyword">STATISTICS</span><br><span class="hljs-comment line-number">153.</span>        <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> table_schema , table_name , index_name )<br><span class="hljs-comment line-number">154.</span>    ) s   <span class="hljs-comment">-- 查询的表二，就是上面3.2的查询结果</span><br><span class="hljs-comment line-number">155.</span><span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">156.</span>    <span class="hljs-keyword">t</span>.table_schema = s.table_schema   <span class="hljs-comment">-- 通过库关联</span><br><span class="hljs-comment line-number">157.</span>        <span class="hljs-keyword">AND</span> <span class="hljs-keyword">t</span>.table_name = s.table_name  <span class="hljs-comment">-- 再通过表变量</span><br><span class="hljs-comment line-number">158.</span>        <span class="hljs-keyword">AND</span> <span class="hljs-keyword">t</span>.table_schema = <span class="hljs-string">'employees'</span>   <span class="hljs-comment">-- 指定某一个库名</span><br><span class="hljs-comment line-number">159.</span><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> SELECTIVITY;</span><br><span class="hljs-comment line-number">160.</span><br><span class="hljs-comment line-number">161.</span>+<span class="hljs-comment">--------------+--------------+------------+-------------+------------+------------+</span><br><span class="hljs-comment line-number">162.</span>| TABLE_SCHEMA | TABLE_NAME   | index_name | cardinality | TABLE_ROWS | SELECTIVITY |<br><span class="hljs-comment line-number">163.</span>+<span class="hljs-comment">--------------+--------------+------------+-------------+------------+------------+</span><br><span class="hljs-comment line-number">164.</span>| employees    | dept_emp     | dept_no    |           8 |     330400 |     0.0000 |<br><span class="hljs-comment line-number">165.</span>| employees    | salaries     | PRIMARY    |      286271 |    2760952 |     0.1037 |<br><span class="hljs-comment line-number">166.</span>| employees    | dept_manager | dept_no    |           9 |         24 |     0.3750 |<br><span class="hljs-comment line-number">167.</span>| employees    | titles       | PRIMARY    |      296887 |     440887 |     0.6734 |<br><span class="hljs-comment line-number">168.</span>| employees    | dept_emp     | PRIMARY    |      298761 |     330400 |     0.9042 |<br><span class="hljs-comment line-number">169.</span>| employees    | titles       | PRIMARY    |      440166 |     440887 |     0.9984 |<br><span class="hljs-comment line-number">170.</span>| employees    | salaries     | PRIMARY    |     2760952 |    2760952 |     1.0000 |<br><span class="hljs-comment line-number">171.</span>| employees    | dept_manager | PRIMARY    |          24 |         24 |     1.0000 |<br><span class="hljs-comment line-number">172.</span>| employees    | titles       | PRIMARY    |      440887 |     440887 |     1.0000 |<br><span class="hljs-comment line-number">173.</span>| employees    | departments  | PRIMARY    |           9 |          9 |     1.0000 |<br><span class="hljs-comment line-number">174.</span>| employees    | employees    | PRIMARY    |      298124 |     298124 |     1.0000 |<br><span class="hljs-comment line-number">175.</span>| employees    | dept_emp     | PRIMARY    |      330400 |     330400 |     1.0000 |<br><span class="hljs-comment line-number">176.</span>| employees    | dept_manager | PRIMARY    |          24 |         24 |     1.0000 |<br><span class="hljs-comment line-number">177.</span>| employees    | departments  | dept_name  |           9 |          9 |     1.0000 |<br><span class="hljs-comment line-number">178.</span>+<span class="hljs-comment">--------------+--------------+------------+-------------+------------+------------+</span><br><span class="hljs-comment line-number">179.</span><br><span class="hljs-comment line-number">180.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">181.</span><span class="hljs-comment">--  通过最后一列的SELECTIVITY是否接近1，判断该索引创建是否合理</span><br><span class="hljs-comment line-number">182.</span><span class="hljs-comment">--  注意：</span><br><span class="hljs-comment line-number">183.</span><span class="hljs-comment">--  Cardinality和table_rows的值，都是通过随机采样，预估得到的</span><br><span class="hljs-comment line-number">184.</span><span class="hljs-comment">--  当analyze前后，Cardinality值相差较多，说明该索引是不应该被创建的(页上的记录数值分布不平均)</span><br><span class="hljs-comment line-number">185.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">186.</span><span class="hljs-comment">--  推荐 SELECTIVITY 15% 以上是适合的</span><br><span class="hljs-comment line-number">187.</span><br><span class="hljs-comment line-number">188.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">189.</span><span class="hljs-comment">-- 索引使用情况</span><br><span class="hljs-comment line-number">190.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">191.</span><br><span class="hljs-comment line-number">192.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> x$schema_index_statistics <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">193.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">194.</span>  table_schema: employees<br><span class="hljs-comment line-number">195.</span>    table_name: employees<br><span class="hljs-comment line-number">196.</span>    index_name: PRIMARY       <span class="hljs-comment">--  索引名字</span><br><span class="hljs-comment line-number">197.</span> rows_selected: <span class="hljs-number">300024</span>        <span class="hljs-comment">--  读取的记录数</span><br><span class="hljs-comment line-number">198.</span>select_latency: <span class="hljs-number">370177723990</span>  <span class="hljs-comment">--  使用该索引读取时总的延迟时间370毫秒（单位是皮秒）</span><br><span class="hljs-comment line-number">199.</span> rows_inserted: <span class="hljs-number">0</span>             <span class="hljs-comment">--  插入的行数</span><br><span class="hljs-comment line-number">200.</span>insert_latency: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">201.</span>  rows_updated: <span class="hljs-number">0</span>             <span class="hljs-comment">--  更新的行数</span><br><span class="hljs-comment line-number">202.</span>update_latency: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">203.</span>  rows_deleted: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">204.</span>delete_latency: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">205.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">206.</span><br><span class="hljs-comment line-number">207.</span><span class="hljs-comment">-- 结合 之前的SELECTIVITY和这里的数值，可以更好的判断索引是否合理</span><br><span class="hljs-comment line-number">208.</span><span class="hljs-comment">-- 重启后数据归0</span></span><br></code></pre>

<blockquote>
  <p>索引是要排序的，建立索引越多，排序以及维护成本会很大，插入数据的速度会变慢，所以索引建立的多，不是仅仅是浪费空间，还会降低性能，增加磁盘IO</p>
</blockquote>

<p><strong>注意：MySQL5.6的版本<code>STATISTICS数据存在问题</code>，截止5.6.28仍然存在，官方定性为Bug</strong></p>

<blockquote>
  <p>作业一：在<code>MySQL5.6</code>中使用<code>mysql.innodb_index_stats</code>得到索引的选择性（SELECTIVITY）</p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-2351" class="wmd-preview-section preview-content">

<h2 id="二-mysql56安装sys库">二. MySQL5.6安装sys库</h2>

</div><div id="wmd-preview-section-2352" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>shell &gt; git <span class="hljs-built_in">clone</span> https://github.com/mysql/mysql-sys.git<br><span class="hljs-comment line-number">2.</span>shell &gt; ls | grep sys_56.sql<br><span class="hljs-comment line-number">3.</span>sys_56.sql <span class="hljs-comment"># 这个就是我们要安装的到mysql5.6的sys</span><br><span class="hljs-comment line-number">4.</span><br><span class="hljs-comment line-number">5.</span>shell&gt; mysql -u root -S /tmp/mysql.sock_56 &lt; sys_56.sql  <span class="hljs-comment"># 直接导入即可</span><br></code></pre>

</div><div id="wmd-preview-section-2353" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">3.</span>| version()  |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">5.</span>| 5.6.27-log |<br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">7.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">databases</span>;</span><br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">11.</span>| Database           |<br><span class="hljs-comment line-number">12.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">13.</span>| information_schema |<br><span class="hljs-comment line-number">14.</span>| burn_test          |<br><span class="hljs-comment line-number">15.</span>| burn_test_56       |<br><span class="hljs-comment line-number">16.</span>| mysql              |<br><span class="hljs-comment line-number">17.</span>| performance_schema |<br><span class="hljs-comment line-number">18.</span>| sys                |  <span class="hljs-comment">-- 新安装的sys库，但是这个里面只有88个记录，因为5.7中增加了几张表，有101个记录</span><br><span class="hljs-comment line-number">19.</span>| test               |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">21.</span>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-2354" class="wmd-preview-section preview-content">

<h2 id="三-explain二">三. Explain（二）</h2>

</div><div id="wmd-preview-section-2880" class="wmd-preview-section preview-content">

<h3 id="1-explain输出介绍">1. Explain输出介绍</h3>

<table>
<thead>
<tr>
  <th align="center">列</th>
  <th align="center">含义</th>
</tr>
</thead>
<tbody><tr>
  <td align="center">id</td>
  <td align="center">执行计划的id标志</td>
</tr>
<tr>
  <td align="center">select_type</td>
  <td align="center">SELECT的类型</td>
</tr>
<tr>
  <td align="center">table</td>
  <td align="center">输出记录的表</td>
</tr>
<tr>
  <td align="center">partitions</td>
  <td align="center">符合的分区，[PARTITIONS]</td>
</tr>
<tr>
  <td align="center">type</td>
  <td align="center">JOIN的类型</td>
</tr>
<tr>
  <td align="center">possible_keys</td>
  <td align="center">优化器可能使用到的索引</td>
</tr>
<tr>
  <td align="center">key</td>
  <td align="center">优化器实际选择的索引</td>
</tr>
<tr>
  <td align="center">key_len</td>
  <td align="center">使用索引的字节长度</td>
</tr>
<tr>
  <td align="center">ref</td>
  <td align="center">进行比较的索引列</td>
</tr>
<tr>
  <td align="center">rows</td>
  <td align="center">优化器<code>预估</code>的记录数量</td>
</tr>
<tr>
  <td align="center">filtered</td>
  <td align="center">根据条件过滤得到的记录的百分比[EXTENDED]</td>
</tr>
<tr>
  <td align="center">extra</td>
  <td align="center">额外的显示选项</td>
</tr>
</tbody></table>


<p><img longdesc="./Selection_001.png" alt="Explain示例" title="" type="image/png" src="" class=""></p></div><div id="wmd-preview-section-6981" class="wmd-preview-section preview-content">

<h4 id="1-id">（1）. id</h4>

<p><code>从上往下理解</code>，不一定 id 序号大的先执行</p>

<blockquote>
  <p>可以简单的理解为 id 相等的从上往下看，id 相等的从下往上看。但是在某些场合也<code>不一定适用</code></p>
</blockquote></div><div id="wmd-preview-section-6956" class="wmd-preview-section preview-content">

<h4 id="2-selecttype">（2）. select_type</h4>

<table>
<thead>
<tr>
  <th align="center">select_type</th>
  <th align="center">含义</th>
</tr>
</thead>
<tbody><tr>
  <td align="center">SIMPLE</td>
  <td align="center">简单SELECT(不使用UNION或子查询等)</td>
</tr>
<tr>
  <td align="center">PRIMARY</td>
  <td align="center">最外层的select</td>
</tr>
<tr>
  <td align="center">UNION</td>
  <td align="center">UNION中的第二个或后面的SELECT语句</td>
</tr>
<tr>
  <td align="center">DEPENDENT UNION</td>
  <td align="center">UNION中的第二个或后面的SELECT语句，依赖于外面的查询</td>
</tr>
<tr>
  <td align="center">UNION RESULT</td>
  <td align="center">UNION的结果</td>
</tr>
<tr>
  <td align="center">SUBQUERY</td>
  <td align="center">子查询中的第一个SELECT</td>
</tr>
<tr>
  <td align="center">DEPENDENT SUBQUERY</td>
  <td align="center">子查询中的第一个SELECT，依赖于外面的查询</td>
</tr>
<tr>
  <td align="center">DERIVED</td>
  <td align="center">派生表的SELECT(FROM子句的子查询)</td>
</tr>
<tr>
  <td align="center">MATERIALIZED</td>
  <td align="center">物化子查询</td>
</tr>
<tr>
  <td align="center">UNCACHEABLE SUBQUERY</td>
  <td align="center">不会被缓存的并且对于外部查询的每行都要重新计算的子查询</td>
</tr>
<tr>
  <td align="center">UNCACHEABLE UNION</td>
  <td align="center">属于不能被缓存的 UNION中的第二个或后面的SELECT语句</td>
</tr>
</tbody></table>


<ul><li><strong>MATERIALIZED</strong> <br>
<ul>
<li>产生中间临时表（实体）</li>
<li>临时表自动创建索引并和其他表进行关联，提高性能</li>
<li>和子查询的区别是，优化器将可以进行<code>MATERIALIZED</code>的语句自动改写成<code>join</code>，并自动创建索引</li></ul></li>
</ul></div><div id="wmd-preview-section-6996" class="wmd-preview-section preview-content">

<h4 id="3-table">（3）. table</h4>

<ul><li>通常是用户操作的用户表</li>
<li>&lt;unionM, N&gt; UNION得到的结果表</li>
<li> 排生表，由id=N的语句产生</li>
<li> 由子查询物化产生的表，由id=N的语句产生</li>
</ul>

</div><div id="wmd-preview-section-7641" class="wmd-preview-section preview-content">

<h4 id="4-type">（4）. type</h4>

<blockquote>
  <p><strong>摘自姜老师的PDF，按照图上箭头的顺序来看，成本（cost）是从小到大</strong></p>
</blockquote>

<p><img longdesc="./Selection_002.png" alt="TYPE" title="" type="image/png" src="" class=""></p>

</div><div id="wmd-preview-section-9172" class="wmd-preview-section preview-content">

<h4 id="5-extra">（5）. extra</h4>

<p><img longdesc="./Selection_003.png" alt="Extra" title="" type="image/png" src="" class=""></p>

<ul><li>Using filesort：可以使用复合索引将filesort进行优化。提高性能</li>
<li>Using index：比如使用覆盖索引</li>
<li>Using where: 使用where过滤条件</li>
</ul>

<blockquote>
  <p>Extra的信息是可以作为优化的提示，但是更多的是优化器优化的一种说明</p>
</blockquote></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>